/**
 * User: ???ò??
 * Date: Feb 25, 2003
 * Time: 5:14:55 PM
 */
package com.ecomm.util;

import com.ecomm.exception.DBException;
import org.apache.commons.logging.Log;

import java.io.InputStream;
import java.io.Reader;
import java.sql.*;
import java.util.Iterator;
import java.util.LinkedList;

public class DBImpl implements DB
{

    private static Log log = Logger.getLog(DBImpl.class);

    Connection conn = null;

    /**
     * 数据库提交方式的状态
     */
    boolean bAutoCommitStatus = true;

    LinkedList lstResultSet = null;
    LinkedList lstStatement = null;

    public DBImpl(Connection conn)
    {
        this.conn = conn;
    }

    /**
     * 创建一个java.sql.Statement对象
     *
     * @return
     * @throws DBException
     */
    public Statement createStatement() throws DBException
    {
        Statement st = null;
        try
        {
            st = conn.createStatement();
            if (lstStatement == null)
                lstStatement = new LinkedList();

            lstStatement.add(st);
        }
        catch (Exception e)
        {
            log.error("error in com.ecomm.util.DBImplCreateStatement", e);
            throw new DBException("createStatement语句失败。");
        }
        return st;
    }

    public Statement createStatement(int resultSetType, int resultSetConcurrency) throws DBException
    {
        Statement st = null;
        try
        {
            st = conn.createStatement(resultSetType, resultSetConcurrency);
            if (lstStatement == null)
                lstStatement = new LinkedList();

            lstStatement.add(st);
        }
        catch (Exception e)
        {
            log.error("error in com.ecomm.util.DBImplCreateStatement. parameter resultSetType value is " + resultSetType + ",resultSetConcurrency value is " + resultSetConcurrency, e);
            throw new DBException("createStatement语句失败。");
        }
        return st;
    }

    /**
     * 用指定的SQL语句创建java.sql.PreparedStatement对象
     *
     * @param sSQL
     * @return
     * @throws DBException
     */
    public PreparedStatement createPreparedStatement(String sSQL) throws DBException
    {
        PreparedStatement pst = null;
        try
        {
            pst = conn.prepareStatement(sSQL);
            if (lstStatement == null)
                lstStatement = new LinkedList();

            lstStatement.add(pst);
        }
        catch (Exception e)
        {
            log.error("error in com.ecomm.util.DBImplcreatePreparedStatement.parameter sSQL value is " + sSQL, e);
            throw new DBException("createStatement语句失败。");
        }
        return pst;
    }

    public PreparedStatement createPreparedStatement(String sSQL, int resultSetType, int resultSetConcurrency) throws DBException
    {
        PreparedStatement pst = null;
        try
        {
            pst = conn.prepareStatement(sSQL);

            if (lstStatement == null)
                lstStatement = new LinkedList();

            lstStatement.add(pst);
        }
        catch (Exception e)
        {
            log.error("error in com.ecomm.util.DBImplcreatePreparedStatement.parameter sSQL value is " + sSQL + "resultSetType value is " + resultSetType + ",resultSetConcurrency value is " + resultSetConcurrency, e);
            throw new DBException("createStatement语句失败。");
        }
        return pst;
    }

    /**
     * 用指定的SQL语句创建java.sql.CallableStatement对象
     *
     * @param sSQL
     * @return
     * @throws DBException
     */
    public CallableStatement createCallableStatement(String sSQL) throws DBException
    {
        CallableStatement cst = null;
        try
        {
            cst = conn.prepareCall(sSQL);
            if (lstStatement == null)
                lstStatement = new LinkedList();

            lstStatement.add(cst);
        }
        catch (Exception e)
        {
            log.error("error in com.ecomm.util.DBImplcreateCallableStatement.parameter sSQL value is " + sSQL, e);
            throw new DBException("createCallableStatement语句失败。");
        }
        return cst;
    }

    public CallableStatement createCallableStatement(String sSQL, int resultSetType, int resultSetConcurrency) throws DBException
    {
        CallableStatement cst = null;
        try
        {
            cst = conn.prepareCall(sSQL, resultSetType, resultSetConcurrency);
            if (lstStatement == null)
                lstStatement = new LinkedList();

            lstStatement.add(cst);
        }
        catch (Exception e)
        {
            log.error("error in com.ecomm.util.DBImplcreateCallableStatement.parameter sSQL value is " + sSQL + "resultSetType value is " + resultSetType + ",resultSetConcurrency value is " + resultSetConcurrency, e);
            throw new DBException("createCallableStatement语句失败。");
        }
        return cst;

    }


    /**
     * 开始一个数据库事务
     *
     * @throws DBException
     */
    public void startTransaction() throws DBException
    {
        try
        {
            conn.setAutoCommit(false);
            bAutoCommitStatus = false;
        }
        catch (Exception e)
        {
            log.error("error in com.ecomm.util.DBImpl.startTransaction.", e);
            throw new DBException("不能开始一个数据库事务。");
        }
    }

    /**
     * 正确结束数据库事务
     *
     * @throws DBException
     */
    public void endTransaction() throws DBException
    {
        try
        {
            conn.commit();
        }
        catch (Exception e)
        {
            log.error("error in com.ecomm.util.DBImpl.endTransaction.", e);
            throw new DBException("结束事务失败。");
        }
        finally
        {
            try
            {
                if (conn.getAutoCommit() == false)
                {
                    conn.setAutoCommit(true);
                    bAutoCommitStatus = true;
                }
            }
            catch (Exception e)
            {
            }
        }
    }

    /**
     * 回滚数据库事务
     *
     * @throws DBException
     */
    public void rollbackTransaction() throws DBException
    {
        try
        {
            conn.rollback();
        }
        catch (Exception e)
        {
            log.error("会滚事务失败。", e);
            throw new DBException("会滚事务失败。");
        }
        finally
        {
            try
            {
                conn.setAutoCommit(true);
                bAutoCommitStatus = true;
            }
            catch (Exception e)
            {
            }
        }
    }

    /**
     * 执行一个查询SQL语句并且返回结果集
     *
     * @param sSQL
     * @return
     * @throws DBException
     */
    public ResultSet executeQuery(String sSQL) throws DBException
    {
        ResultSet rs = null;
        try
        {
            Statement st = createStatement();
//            log.info(sSQL);
            rs = st.executeQuery(sSQL);
        }
        catch (Exception e)
        {
            log.error("error in com.ecomm.util.DBImpl.executeQuery.parameter sSQL value is " + sSQL, e);
            throw new DBException("执行查询语句失败。");
        }
        return rs;
    }

    /**
     * 执行一个更新语句并且返回影响的记录条数
     *
     * @param sSQL
     * @return
     * @throws DBException
     */
    public int executeUpdate(String sSQL) throws DBException
    {
        int nAffect = 0;
        try
        {
            Statement st = createStatement();
//            log.info(sSQL);
            nAffect = st.executeUpdate(sSQL);
        }
        catch (Exception e)
        {
            log.error("error in com.ecomm.util.DBImpl.executeUpdate.parameter sSQL value is " + sSQL, e);
            throw new DBException("执行更新语句失败。");
        }
        return nAffect;
    }

    /**
     * 取得当前数据库连接的数据库元数据对象
     *
     * @return
     * @throws DBException
     */
    public DatabaseMetaData getDatabaseMetaData() throws DBException
    {
        try
        {
            return conn.getMetaData();
        }
        catch (Exception e)
        {
            throw new DBException(e.getMessage());
        }
    }

    /**
     * 取得当前数据库连接的数据库类型，具体类型定义参考接口DBType中的定义。
     *
     * @return
     * @throws DBException
     */
    public String getDatabaseType() throws DBException
    {
        String dbtype = "";
        try
        {
            String dbProductName = conn.getMetaData().getDatabaseProductName();
            int i = dbProductName.indexOf(" ");
            if(i!=-1)
            {
                dbProductName = dbProductName.substring(0,i);
            }
            dbtype = Config.getString("dbtype." + dbProductName.toLowerCase());
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        return dbtype;
    }

    /**
     * 关闭数据库对象，并且将所有打开的对象全部关闭。
     */
    public void close()
    {
        if (lstResultSet != null)
        {
            for (Iterator it = lstResultSet.iterator(); it.hasNext();)
            {
                ResultSet rs = (ResultSet) it.next();
                try
                {
                    rs.close();
                }
                catch (Exception e)
                {
                }
            }
            lstResultSet.removeAll(lstResultSet);
        }
        lstResultSet = null;


        if (lstStatement != null)
        {
            for (Iterator it = lstStatement.iterator(); it.hasNext();)
            {
                Statement st = (Statement) it.next();
                try
                {
                    st.close();
                }
                catch (Exception e)
                {
                }
            }
            lstStatement.removeAll(lstStatement);
        }
        lstStatement = null;

        try
        {
            conn.close();
        }
        catch (Exception e)
        {
        }
    }

    /**
     * 更新clob字段的值
     *
     * @param tablename
     * @param fieldname
     * @param condtion
     * @param value
     * @throws DBException
     */
    public void updateClob(String tablename, String fieldname, String condtion, String value) throws DBException
    {
        String sSQL = "UPDATE " + tablename + " SET " + fieldname + "=? WHERE " + condtion;
        try
        {
            PreparedStatement pst = createPreparedStatement(sSQL);
            pst.setString(1, value);
            pst.executeUpdate();
        }
        catch (Exception e)
        {
            log.error("error in com.ecomm.util.DBImpl.updateClob", e);
            throw new DBException("更新clob字段内容失败。");
        }
    }

    public void updateClob(String tablename, String fieldname, String condtion, Reader reader, int length) throws DBException
    {
        String sSQL = "UPDATE " + tablename + " SET " + fieldname + "=? WHERE " + condtion;
        try
        {
            PreparedStatement pst = createPreparedStatement(sSQL);
            pst.setCharacterStream(1, reader, length);
            pst.executeUpdate();
        }
        catch (Exception e)
        {
            log.error("error in com.ecomm.util.DBImpl.updateClob", e);
            throw new DBException("更新clob字段内容失败。");
        }
    }

    /**
     * 更新blob字段的值
     *
     * @param tablename
     * @param fieldname
     * @param condtion
     * @param value
     * @throws DBException
     */
    public void updateBlob(String tablename, String fieldname, String condtion, byte[] value) throws DBException
    {
        String sSQL = "UPDATE " + tablename + " SET " + fieldname + "=? WHERE " + condtion;
        try
        {
            PreparedStatement pst = createPreparedStatement(sSQL);
            pst.setBytes(1, value);
            pst.executeUpdate();
        }
        catch (Exception e)
        {
            log.error("error in com.ecomm.util.DBImpl.updateBlob", e);
            throw new DBException("更新blob字段内容失败。");
        }
    }

    public void updateBlob(String tablename, String fieldname, String condtion, InputStream input, int length) throws DBException
    {
        String sSQL = "UPDATE " + tablename + " SET " + fieldname + "=? WHERE " + condtion;
        try
        {
            PreparedStatement pst = createPreparedStatement(sSQL);
            pst.setBinaryStream(1, input, length);
            pst.executeUpdate();
        }
        catch (Exception e)
        {
            log.error("error in com.ecomm.util.DBImpl.updateBlob", e);
            throw new DBException("更新Blob字段内容失败。");
        }
    }

    /**
     * 取得clob字段的值
     *
     * @param tablename
     * @param fieldname
     * @param condition
     * @return
     * @throws DBException
     */
    public String getClob(String tablename, String fieldname, String condition) throws DBException
    {
        String sSQL = "SELECT " + fieldname + " FROM " + tablename + " WHERE " + condition;
        String sRet = null;
        try
        {
            ResultSet rs = executeQuery(sSQL);
            if (rs.next())
            {
                sRet = rs.getString(1);
            }
        }
        catch (Exception e)
        {
            log.error("error in com.ecomm.util.DBImpl.getClob", e);
            throw new DBException("取得clob字段的内容失败。");
        }
        return sRet;
    }

    /**
     * 取得blob字段的值，并且将结果填入一个字节数组中
     *
     * @param tablename
     * @param fieldname
     * @param condition
     * @return
     * @throws DBException
     */
    public byte[] getBlob(String tablename, String fieldname, String condition) throws DBException
    {
        String sSQL = "SELECT " + fieldname + " FROM " + tablename + " WHERE " + condition;
        byte[] bRet = null;
        try
        {
            ResultSet rs = executeQuery(sSQL);
            if (rs.next())
            {
                bRet = rs.getBytes(1);
            }
        }
        catch (Exception e)
        {
            log.error("error in com.ecomm.util.DBImpl.getBlob", e);
            throw new DBException("取得blob字段的内容失败。");
        }
        return bRet;
    }

    /**
     * 取得一个clob字段的输出流对象
     *
     * @param tablename
     * @param fieldname
     * @param condition
     * @return
     * @throws DBException
     */
    public Reader getClobOutputStream(String tablename, String fieldname, String condition) throws DBException
    {
        String sSQL = "SELECT " + fieldname + " FROM " + tablename + " WHERE " + condition;
        Reader reader = null;
        try
        {
            ResultSet rs = executeQuery(sSQL);
            if (rs.next())
            {
                reader = rs.getCharacterStream(1);
            }
        }
        catch (Exception e)
        {
            log.error("error in com.ecomm.util.DBImpl.getClobOutputStream", e);
            throw new DBException("取得clob字段的内容失败。");
        }
        return reader;
    }

    /**
     * 取得一个blob字段的输出流对象
     *
     * @param tablename
     * @param fieldname
     * @param condition
     * @return
     * @throws DBException
     */
    public InputStream getBlobOutputStream(String tablename, String fieldname, String condition) throws DBException
    {
        String sSQL = "SELECT " + fieldname + " FROM " + tablename + " WHERE " + condition;
        InputStream input = null;
        try
        {
            ResultSet rs = executeQuery(sSQL);
            if (rs.next())
            {
                input = rs.getBinaryStream(1);
            }
        }
        catch (Exception e)
        {
            log.error("error in com.ecomm.util.DBImpl.getBlobOutputStream", e);
            throw new DBException("取得Blob字段的内容失败。");
        }
        return input;
    }

    /**
     * 检测数据库是否被关闭。
     *
     * @return
     */
    public boolean isClosed()
    {
        boolean bRet = false;
        try
        {
            bRet = conn.isClosed();
        }
        catch (Exception e)
        {
            log.error("检测数据库连接是否已被关闭异常。", e);
        }
        return bRet;
    }

    /**
     * 输出现有db占用资源的情况
     *
     */
    public void getResourceInfo()
    {
        if (lstResultSet!=null)
            log.info("DB("+this.hashCode()+")共使用ResultSet"+lstResultSet.size()+"个");
        if (lstStatement!=null)
            log.info("DB("+this.hashCode()+")共使用Statement"+lstStatement.size()+"个");
    }

}
